﻿using Microsoft.Data.SqlClient;
using Microsoft.Identity.Client;
using Microsoft.Win32.SafeHandles;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using UsedBook.Models;
using UsedBook.Utils;

namespace UsedBook.DAL
{
    public class BookDAL
    {
        SqlConnection _connection = DBHelper.GetSqlConnection();
        string? sql;

        // 查询所有书籍
        #region 查询所有书籍  public List<Book> GetBooks()
        public List<Book> GetBooks()
        {
            _connection.Open();
            List<Book> books = new();
            sql = "select * from Books";
            using SqlCommand cmd = new(sql, _connection);
            using SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                // 创建一个新的 Book 对象
                Book book = new Book
                {
                    BookId = reader.GetInt32(reader.GetOrdinal("BookId")),       // 获取 BookId
                    UserId = reader.GetInt32(reader.GetOrdinal("userId")),
                    Title = reader.GetString(reader.GetOrdinal("Title")),       // 获取书名
                    Author = reader.GetString(reader.GetOrdinal("Author")),     // 获取作者
                    Publisher = reader.GetString(reader.GetOrdinal("Publisher")), // 获取出版社
                    Price = reader.GetDecimal(reader.GetOrdinal("Price")),      // 获取价格
                    Condition = reader.GetString(reader.GetOrdinal("Condition")), // 获取书籍状态
                    Description = reader.GetString(reader.GetOrdinal("Description")),
                    MajorId = reader.GetInt32(reader.GetOrdinal("MajorId")),    // 获取专业 ID
                    CreateAt = reader.GetDateTime(reader.GetOrdinal("CreateAt")) // 获取创建时间
                };

                // 将读取到的书籍信息添加到 books 列表中
                books.Add(book);
            }
            _connection.Close();
            return books;
        }
        #endregion

        // 根据Id查询书籍
        #region 根据Id查询书籍 public Book GetBookById(int id)
        public Book GetBookById(int id)
        {
            Book book = new();
            sql = "select * from Books where BookId = @Id";
            using (SqlConnection connection = DBHelper.GetSqlConnection())
            {
                connection.Open();
                string sql = "SELECT * FROM Books WHERE BookId = @BookId";

                // 创建 SqlCommand 并添加参数
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    cmd.Parameters.AddWithValue("@BookId", id);  // 使用 AddWithValue 来简化代码

                    // 使用 SqlDataReader 读取查询结果
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        // 如果查询到数据，返回 Book 对象
                        if (reader.Read())
                        {
                            book = new Book()
                            {
                                BookId = reader.GetInt32(reader.GetOrdinal("BookId")),
                                UserId = reader.GetInt32(reader.GetOrdinal("UserId")),
                                Title = reader.GetString(reader.GetOrdinal("Title")),
                                Author = reader.GetString(reader.GetOrdinal("Author")),
                                Publisher = reader.GetString(reader.GetOrdinal("Publisher")),
                                Price = reader.GetDecimal(reader.GetOrdinal("Price")),
                                Condition = reader.GetString(reader.GetOrdinal("Condition")),
                                Description = reader.GetString(reader.GetOrdinal("Description")),
                                MajorId = reader.GetInt32(reader.GetOrdinal("MajorId")),
                                CreateAt = reader.GetDateTime(reader.GetOrdinal("CreateAt"))
                            };
                        }
                        return book;
                    }
                }
            }
        }
        #endregion

        // 根据Id修改书籍
        #region 按照Id修改书籍  public bool UpdateBookById(int id,Book newBook)
        public bool UpdateBookById(int id, Book newBook)
        {
            using (SqlConnection conn = DBHelper.GetSqlConnection())
            {
                conn.Open();
                string sql = "update Books set UserId=@UserId,Title=@Title,Author=@Author,Publisher=@Publisher,Price=@Price,Condition=@Condition,Description=@Description,MajorId=@MajorId where BookId=@Bookid";
                SqlParameter[] sqlParameters = new SqlParameter[]
                {
                    new SqlParameter("UserId",newBook.UserId),
                    new SqlParameter("Title",newBook.Title),
                    new SqlParameter("Author",newBook.Author),
                    new SqlParameter("Publisher",newBook.Publisher),
                    new SqlParameter("Price",newBook.Price),
                    new SqlParameter("Condition",newBook.Condition),
                    new SqlParameter("Description",newBook.Description),
                    new SqlParameter("MajorId",newBook.MajorId)
                };
                using (SqlCommand cmd = new(sql, conn))
                {
                    cmd.Parameters.AddRange(sqlParameters);
                    int RowsAffected = cmd.ExecuteNonQuery();
                    return (RowsAffected > 0);
                }
            }
        }
        #endregion

        // 根据Id删除书籍
        #region 根据Id删除书籍  public bool DeleteBookById(int id)
        public bool DeleteBookById(int id)
        {
            using (SqlConnection conn = DBHelper.GetSqlConnection())
            {
                conn.Open();
                string sql = "delete from Books where BookId = @Id";
                SqlParameter sqlParameter = new SqlParameter("Id", id);
                using (SqlCommand cmd = new(sql, conn))
                {
                    cmd.Parameters.Add(sqlParameter);
                    int row = cmd.ExecuteNonQuery();
                    return (row > 0);
                }
            }
        }
        #endregion

        // 增加书籍
        #region 增加书籍  public bool AddBook(Book newBook)
        public bool AddBook(Book newBook)
        {
            using (SqlConnection conn = DBHelper.GetSqlConnection())
            {
                conn.Open();
                string sql = "insert into Books(UserId, Title, Author, Publisher, Price, Condition, [Description], MajorID) values (@UserId,@Title,@Author,@Publisher,@Price,@Condition,@Description,@MajorId)";
                SqlParameter[] sqlParameters =
                {
                    new SqlParameter("UserId",newBook.UserId),
                    new SqlParameter("Title",newBook.Title),
                    new SqlParameter("Author",newBook.Author),
                    new SqlParameter("Publisher",newBook.Publisher),
                    new SqlParameter("Price",newBook.Price),
                    new SqlParameter("Condition",newBook.Condition),
                    new SqlParameter("Description",newBook.Description),
                    new SqlParameter("MajorID",newBook.MajorId)
                };
                using (SqlCommand cmd = new(sql, conn))
                {
                    cmd.Parameters.AddRange(sqlParameters);
                    int row = cmd.ExecuteNonQuery();
                    return row > 0;
                }
            }
        }
        #endregion


    }
}
